package com.portal.framework.util;

import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;

import com.portal.framework.exception.AppException;

/**
 * 
 * 
 * @author 卢烁波
 * @date 2007-3-10
 */
public class SQLUtil {

	// 数据类型
	// 日期
	public static final String DATE_TYPE = "Date";

	// 字符串

	public static final String STRING_TYPE = "String";

	// 数字
	public static final String NUMBER_TYPE = "Number";

	public final static String LIKE_FIRST = "LikeFirst";

	public final static String LIKE_LAST = "LikeLast";

	public final static String LIKE_ANY = "LikeAny";

	public final static String NOT_IN = "NOT_IN";

	public static String Collction2SQL(Collection col) {
		ArrayList arr = new ArrayList(col);
		if (arr.size() == 0) {
			return "";
		}
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < arr.size(); i++) {
			SQLDTO sql = (SQLDTO) arr.get(i);
			sb.append(SQLUtil.SQLDTO2SQL(sql));
		}
		return sb.toString();
	}

	private static String SQLDTO2SQL(SQLDTO sql) {
		StringBuffer sb = new StringBuffer();
		if (null != sql.getLbracket()) {
			sb.append(" " + sql.getLbracket() + " ");
		}
		if (null != sql.getNotopr()) {
			sb.append(" " + sql.getNotopr() + " ");
		}

		String operator = sql.getOperator();
		char dataType = sql.getDatatype().toUpperCase().charAt(0);
		String minValue = sql.getMinvalue().toString();
		if (dataType == 'S') {
			minValue = minValue.replaceAll("'", "''");
		}

		if ("between".equalsIgnoreCase(operator)) {
			if (sql.getMaxvalue() == null)
				throw new RuntimeException("没有为<" + sql.getItem() + ">设置最大值!");
			String maxValue = sql.getMaxvalue().toString();
			switch (dataType) {
			case 'N':
				sb.append(sql.getItem());
				sb.append(" between ");
				sb.append(minValue);
				sb.append(" and ");
				sb.append(maxValue);
				break;
			case 'S':
				sb.append(sql.getItem());
				sb.append(" between '");
				sb.append(minValue);
				sb.append("' and '");
				sb.append(maxValue);
				sb.append("'");
				break;
			case 'D':
				sb.append(changeLongToDate(sql.getItem().trim()));
				sb.append(" between ");
				sb.append(" to_date('" + minValue + "', 'yyyy-mm-dd') ");
				sb.append(" and ");
				sb.append(" to_date('" + maxValue + "', 'yyyy-mm-dd') ");
				break;
			default:
				throw new RuntimeException("未知的数据类型");
			}
		} else if ("<>".equalsIgnoreCase(operator)) {
			switch (dataType) {
			case 'N':
				sb.append(sql.getItem());
				sb.append(" <> ");
				sb.append(minValue);
				break;
			case 'S':
				sb.append(sql.getItem());
				sb.append(" <> ");
				sb.append(" '" + minValue + "' ");
				break;
			case 'D':
				sb.append(changeLongToDate(sql.getItem().trim()));
				sb.append(" <> ");
				sb.append(" to_date('" + minValue + "', 'yyyy-mm-dd') ");
				break;
			default:
				throw new RuntimeException("未知的数据类型");
			}
		} else if ("=".equalsIgnoreCase(operator)) {
			switch (dataType) {
			case 'N':
				sb.append(sql.getItem());
				sb.append(" = ");
				sb.append(minValue.trim());
				break;
			case 'S':
				sb.append(sql.getItem());
				sb.append(" = ");
				sb.append(" '" + minValue + "' ");
				break;
			case 'D':
				sb.append(changeLongToDate(sql.getItem().trim()));
				sb.append(" = ");
				sb.append(" to_date('" + minValue + "', 'yyyy-mm-dd') ");
				break;
			default:
				throw new RuntimeException("未知的数据类型");
			}
		} else if (">".equalsIgnoreCase(operator)) {
			switch (dataType) {
			case 'N':
				sb.append(sql.getItem());
				sb.append(" > ");
				sb.append(minValue);
				break;
			case 'S':
				sb.append(sql.getItem());
				sb.append(" > ");
				sb.append(" '" + minValue + "' ");
				break;
			case 'D':
				sb.append(changeLongToDate(sql.getItem().trim()));
				sb.append(" > ");
				sb.append(" to_date('" + minValue + "', 'yyyy-mm-dd') ");
				break;
			default:
				throw new RuntimeException("未知的数据类型");
			}
		} else if (">=".equalsIgnoreCase(operator)) {
			switch (dataType) {
			case 'N':
				sb.append(sql.getItem());
				sb.append(" >= ");
				sb.append(minValue);
				break;
			case 'S':
				sb.append(sql.getItem());
				sb.append(" >= ");
				sb.append(" '" + minValue + "' ");
				break;
			case 'D':
				sb.append(changeLongToDate(sql.getItem().trim()));
				sb.append(" >= ");
				sb.append(" to_date('" + minValue + "', 'yyyy-mm-dd') ");
				break;
			default:
				throw new RuntimeException("未知的数据类型");
			}
		} else if ("<".equalsIgnoreCase(operator)) {
			switch (dataType) {
			case 'N':
				sb.append(sql.getItem());
				sb.append(" < ");
				sb.append(minValue);
				break;
			case 'S':
				sb.append(sql.getItem());
				sb.append(" < ");
				sb.append(" '" + minValue + "' ");
				break;
			case 'D':
				sb.append(changeLongToDate(sql.getItem().trim()));
				sb.append(" < ");
				sb.append(" to_date('" + minValue + "', 'yyyy-mm-dd') ");
				break;
			default:
				throw new RuntimeException("未知的数据类型");
			}
		} else if ("<=".equalsIgnoreCase(operator)) {
			switch (dataType) {
			case 'N':
				sb.append(sql.getItem());
				sb.append(" <= ");
				sb.append(minValue);
				break;
			case 'S':
				sb.append(sql.getItem());
				sb.append(" <= ");
				sb.append(" '" + minValue + "' ");
				break;
			case 'D':
				sb.append(changeLongToDate(sql.getItem().trim()));
				sb.append(" <= ");
				sb.append(" to_date('" + minValue + "', 'yyyy-mm-dd') ");
				break;
			default:
				throw new RuntimeException("未知的数据类型");
			}
		} else if ("IN".equalsIgnoreCase(operator)) {
			switch (dataType) {
			case 'N':
				sb.append(sql.getItem());
				sb.append(" in ");
				sb.append(" (" + minValue + ") ");
				break;
			case 'S':
				sb.append(sql.getItem());
				sb.append(" in ");
				sb.append(" ('" + minValue.replaceAll(",", "','") + "') ");
				break;
			case 'D':
				throw new RuntimeException("未知的数据类型");
			default:
				throw new RuntimeException("未知的数据类型");
			}
		} else if (LIKE_FIRST.equalsIgnoreCase(operator)) {
			switch (dataType) {
			case 'N':
				sb.append(sql.getItem());
				sb.append(" like ");
				sb.append(" '" + minValue + "%' ");
				break;
			case 'S':
				sb.append(sql.getItem());
				sb.append(" like ");
				sb.append(" '" + minValue + "%' ");
				break;
			case 'D':
				throw new RuntimeException("未知的数据类型");
			default:
				throw new RuntimeException("未知的数据类型");
			}
		} else if (LIKE_LAST.equalsIgnoreCase(operator)) {
			switch (dataType) {
			case 'N':
				sb.append(sql.getItem());
				sb.append(" like ");
				sb.append(" '%" + minValue + "' ");
				break;
			case 'S':
				sb.append(sql.getItem());
				sb.append(" like ");
				sb.append(" '%" + minValue + "' ");
				break;
			case 'D':
				throw new RuntimeException("未知的数据类型");

			default:
				throw new RuntimeException("未知的数据类型");
			}
		} else if (LIKE_ANY.equalsIgnoreCase(operator)) {
			switch (dataType) {
			case 'N':
				sb.append(sql.getItem());
				sb.append(" like ");
				sb.append(" '%" + minValue + "%' ");
				break;
			case 'S':
				sb.append(sql.getItem());
				sb.append(" like ");
				sb.append(" '%" + minValue + "%' ");
				break;
			case 'D':
				throw new RuntimeException("未知的数据类型");
			default:
				throw new RuntimeException("未知的数据类型");
			}
		} else if (NOT_IN.equalsIgnoreCase(operator)) {
			switch (dataType) {
			case 'N':
				sb.append(sql.getItem());
				sb.append(" NOT IN (");
				sb.append(minValue + ") ");
				break;
			case 'S':
				sb.append(sql.getItem());
				sb.append(" NOT IN (");
				sb.append(minValue + ") ");
				break;
			case 'D':
				throw new RuntimeException("未知的数据类型");
			default:
				throw new RuntimeException("未知的数据类型");
			}
		} else {
			switch (dataType) {
			case 'N':
				sb.append(sql.getItem());
				sb.append(" = ");
				sb.append(minValue);
				break;
			case 'S':
				sb.append(sql.getItem());
				sb.append(" = ");
				sb.append(" '" + minValue + "' ");
				break;
			case 'D':
				sb.append(changeLongToDate(sql.getItem().trim()));
				sb.append(" = ");
				sb.append(" to_date('" + minValue + "', 'yyyy-mm-dd') ");
				break;
			default:
				throw new RuntimeException("未知的数据类型");
			}
		}
		if (null != sql.getRbracket()) {
			sb.append(" " + sql.getRbracket() + " ");
		}
		if (null != sql.getLogical()) {
			sb.append(" " + sql.getLogical() + " ");
		}
		return sb.toString();
	}

	private static String changeLongToDate(String sCode) {
		return sCode;
	}

	/**
	 * write by caolan 将查询条件组织到hashmap中
	 * 
	 * 
	 * @param col
	 * @return
	 * @throws AppException
	 */
	public static HashMap Collction2HashMap(Collection col) {
		ArrayList arr = new ArrayList(col);
		HashMap hm = new HashMap();
		String sItem;

		for (int i = 0; i < arr.size(); i++) {
			SQLDTO sql = (SQLDTO) arr.get(i);
			sItem = sql.getItem().toLowerCase().trim();
			sItem = sItem.substring(sItem.indexOf(".") + 1, sItem.length());
			hm.put("max" + sItem, sql.getMaxvalue());
			hm.put("min" + sItem, sql.getMinvalue());
		}
		return hm;
	}

	/**
	 * write by caolan 去掉过滤条件中指定条件项
	 * 
	 * @param colSQL
	 *            过滤条件
	 * @param sItem
	 *            去掉指定条件项名称
	 * 
	 * @throws AppException
	 */
	public static void removeFilterByItem(Collection colSQL, String sItem) {

		String myItem;
		for (Iterator ite = colSQL.iterator(); ite.hasNext();) {
			SQLDTO sql = (SQLDTO) ite.next();
			myItem = sql.getItem().toLowerCase().trim();
			myItem = myItem.substring(myItem.indexOf(".") + 1, myItem.length()); // 去掉别名
			if (myItem.equals(sItem.toLowerCase().trim())) {
				ite.remove();
			}

		}
		// 当删除某一个过滤条件后,将最后一个条件的Logical清空
		ArrayList arr = (ArrayList) colSQL;
		if (arr.size() > 0) {
			((SQLDTO) arr.get(arr.size() - 1)).setLogical("");
		}

	}

	/**
	 * 构造一个 SQLDTO
	 * 
	 * @param fcode
	 *            字段名
	 * 
	 * @param dataType
	 *            数据类型：N, S, D
	 * @param fvalue
	 *            字段值
	 * 
	 * @param logic
	 *            逻辑运算符号
	 * @return
	 */
	public static SQLDTO genSqlDTOSimple(String fcode, String dataType,
			String fvalue, String logic) {
		SQLDTO sqldto = new SQLDTO();
		sqldto.setLbracket("");
		sqldto.setNotopr("");
		sqldto.setItem(fcode);
		sqldto.setDatatype(dataType);
		sqldto.setOperator("=");
		sqldto.setMinvalue(fvalue);
		sqldto.setRbracket("");
		sqldto.setLogical(logic);
		return sqldto;
	}

	/**
	 * 增加一个过滤条件
	 * 
	 * 
	 * @param filters
	 * @param sqldto
	 */
	public static void addSQLDTO(List filters, SQLDTO sqldto) {
		if (filters.size() > 0) {
			sqldto.setLogical("AND");
		}
		filters.add(0, Object2HashMap(sqldto));
	}

	/**
	 * 添加一个过滤条件，用 AND 的关系连接其它条件
	 * 
	 * 
	 * @param filters
	 * @param sqldto
	 */
	public static void appendSQLDTO(List filters, SQLDTO sqldto) {
		if (filters.size() > 0) {
			HashMap oLast = (HashMap) filters.get(filters.size() - 1);
			oLast.put("logical", "AND");
		}
		filters.add(Object2HashMap(sqldto));
	}

	/**
	 * 通过调用一个对象的get方法，把obj转化成一个HashMap，其关键字为该对象所对应
	 * 类型中以get开头的方法名字去掉get后第一个字母小写的结果。
	 * 
	 * 
	 * @param obj
	 * @return
	 */
	public static HashMap Object2HashMap(Object obj) {
		if (obj == null) {
			return null;
		}
		HashMap hm = null;
		try {
			hm = new HashMap(BeanUtils.describe(obj));
		} catch (IllegalAccessException e) {
			throw new RuntimeException(e.getMessage());
		} catch (InvocationTargetException e) {
			throw new RuntimeException(e.getMessage());
		} catch (NoSuchMethodException e) {
			throw new RuntimeException(e.getMessage());
		}
		if (hm != null) {
			hm.remove("class");
		}
		return hm;
	}

	public static void main(String[] args) throws Exception {
		List list = new ArrayList();
		SQLDTO sql1 = new SQLDTO();
		sql1.setDatatype(SQLUtil.NUMBER_TYPE);
		sql1.setItem("money");
		sql1.setMinvalue("100");
		sql1.setOperator(">");
		sql1.setLogical("and");
		list.add(sql1);
		sql1 = new SQLDTO();
		sql1.setDatatype(SQLUtil.STRING_TYPE);
		sql1.setItem("companyName");
		sql1.setMinvalue("microsoft,ibm,sun,borland");
		sql1.setOperator("in");
		sql1.setLogical("and");
		list.add(sql1);
		sql1 = new SQLDTO();
		sql1.setDatatype(SQLUtil.DATE_TYPE);
		sql1.setItem("birthday");
		sql1.setMinvalue("1999-01-01");
		sql1.setMaxvalue("2005-12-12");
		sql1.setOperator("between");
		sql1.setLogical("or");
		list.add(sql1);
		sql1 = new SQLDTO();
		sql1.setDatatype(SQLUtil.STRING_TYPE);
		sql1.setItem("name");
		sql1.setMinvalue("abc");
		sql1.setOperator(LIKE_FIRST);
		list.add(sql1);
		System.out.println(SQLUtil.Collction2SQL(list));
	}

	// *******************add by liqiyi on
	// 2007.6.27****************************************

	/**
	 * 用于批量删除或更新，将id组织成('1','2','3','4')
	 * 
	 * @param longIDs
	 *            主键id集合
	 * @return
	 * @author 袁金锋
	 * 
	 * 
	 */
	public static String getStringForHQL(String[] longIDs) {
		if (longIDs.length == 1) {
			return "  ( '" + longIDs[0] + "' ) ";
		}
		StringBuffer str = new StringBuffer(" ( ");
		for (int i = 0; i < longIDs.length - 1; i++) {
			str.append("'" + longIDs[i] + "',");
		}
		str.append("'" + longIDs[longIDs.length - 1] + "' )");
		return str.toString();
	}

	/**
	 * 用于批量删除或更新，将id组织成(1,2,3,4)
	 * 
	 * @param longIDs
	 *            主键id集合
	 * @return
	 * @author 袁金锋
	 * 
	 * 
	 */
	public static String getStringForHQL(long[] longIDs) {
		if (longIDs.length == 1) {
			return "  ( " + longIDs[0] + " ) ";
		}
		StringBuffer str = new StringBuffer(" ( ");
		for (int i = 0; i < longIDs.length - 1; i++) {
			str.append("" + longIDs[i] + ",");
		}
		str.append("" + longIDs[longIDs.length - 1] + " )");
		return str.toString();
	}
	
	/**
	 * 用于批量删除或更新，将id组织成(1,2,3,4)
	 * 
	 * @param longIDs
	 *            主键id集合
	 * @return
	 * @author 袁金锋

	 * 
	 * 
	 */
	public static String getStringForHQL(Long[] longIDs) {
		if (longIDs.length == 1) {
			return "  ( " + longIDs[0] + " ) ";
		}
		StringBuffer str = new StringBuffer(" ( ");
		for (int i = 0; i < longIDs.length - 1; i++) {
			str.append("" + longIDs[i] + ",");
		}
		str.append("" + longIDs[longIDs.length - 1] + " )");
		return str.toString();
	}

	/**
	 * 把where条件替换成可用的where条件
	 * @param whereStr
	 * @return
	 */
	public static String replaceWhereStr(String whereStr) {
		if (whereStr != null && !whereStr.trim().equals("")) {
			return whereStr.replace('^', '\'').replace('~', ' ').replace('{',
					'=').replace('}', '%');
		} else {
			return "";
		}
	}

}
